SPREADSHEET STUFF AND THIS WINTER'S ELECTRICITY BILL
By Dave Stock.
Copyright (c) 1990 Apple Users' Group, Sydney
Republished from Applecations, a publication of the Apple Users' Group, Sydney, Australia.
All the material in this article will work with Appleworks v2.0 and upward.
Many of us have Appleworks and use only the word processor section. Here is a good use for the spreadsheet portion which could save you a lot of money if you can see more clearly where your electricity dollar is going. We are all only too familiar with the sight of the quarterly Power Bill from the SCC. The post-winter one is always the worst. Here is a sample of mine from last year:-
1| CALCULATION OF SCC POWER BILL FOR WINTER 1989
2|====A========B========C========D========E========F====
3| Tariff Previous Present kWh Cents AMOUNT
4| Reading Reading perBlock per kWh
5|======================================================
6|Domestic 69527 71854 200 12.74 $25.48
7|Domestic 2127 9.22 $196.11
8|Off Peak 15416 16209 793 3.30 $26.17
9| ---------
10| $247.76
11| =========
^Formulae, Dollar Layout and Recalculate^
Now boot up your Appleworks spreadsheet and starting from the top left corner, enter this example into it. Numbers and their symbols are easily accepted. If it is characters you want to enter and not numbers, the spreadsheet can generally understand that. If it does not, then you must start your entry with a quote mark. This will become clear to you when it spits out the "equals" signs which make quite a nice underline. Do not try to put in $ signs until last. Also, the entries in the column under "AMOUNT" are entered as calculation formulae which can be started with a bracket. For example, where it reads $25.48, simply type this in:- (D6*E6/100). D and E are columns, 6 is the row, * means times and / means divide. Where it reads $196.11, enter (D7*E7/100). At $247.76, you guessed it, (D8*E8/100). Now to add up this column for the final tally of your bill. This command is a little different. Just type in @SUM(F6..F8). Now you can see the magic of the spreadsheet.
Now for those dollar signs. Position the screen cursor on the 25.48. Hold down the Open Apple key and press L. Now it's asking you for the Layout. Press C for Column for a dollar format and press Return. Press V for value, D for dollars, and of course, 2 for the decimal places which gives you the cents. Now if you want to change any of the meter readings you will notice that the spreadsheet will respond by recalculating this all over again. Should you want to stop this automatic feature during entries of large amounts of data, then press OA-V, R for Recalculate, F for
frequency, M for manual. Now if you want the calculation to take place just hit Open-Apple-K. Get out your own power bill and enter it into this format. All you need to type in are the two meter readings for each tariff. Everything else is done automatically. Hopefully the rates have not been put up before this article gets to you!
^Lookup Tables and Leap Years^
You have probably noticed that the Council has made no mistake in calculating what you owe them. Their spreadsheet works too! Now if you feel a little more daring, you could add a few of your older SCC bills together in a certain format just to see where your excessive use of electricity could perhaps be curtailed. You might even be able to justify the purchase of your computer to the rest of the family!
One nasty little problem comes up though. How can you count the days between meter readings? Well, this is a bit cumbersome, but not complex. Calculations can be made by processing the date entries. You must enter the month as a number to make this possible. The name "June" can not be calculated. Its number 6 will indicate that 5 months have preceded. Zero will indicate that January has no months preceding it, and the day entry for January simply equals the number of days. This gives you the number of days into the new year, the "nth" day if you like.
What about February with its varying number of days you ask? O.K. this method is a bit awkward but works quite well. Remember that a leap year is calculated by dividing by 4. (Please do not remind me about the year 2000. We will just have to skip that non leap-year!) There is a handy function in the spreadsheet which will look up tables, so we must make up a table to calculate the days. The function is written:- @LOOKUP(reference, range). It will return the adjacent value, just as in a table. Let's say we will deal with the date 6th June. The entry would be 9-6-1990. If the function is directed to June by using the number 6, then it will go to 6 in the table alongside which we will have cleverly put the number of days it is up until June, i.e. 151. The day entry will give the number of days in June, 9. The total is 160. This enables us to count the days between two different dates.
Now by using this method we are able to work out how many units of electricity we are using per day. We can also compare the summer bill with the winter bill etc.
^If, Then, Else Choosing Function^
There is another function we must use in this exercise which can be explained as a choosing process. It is an instruction which says "If ^this^ is true, then do that, otherwise do something else". This function is typed in this way:- @IF(true,then,else). One must use numbers though, not words.
For example I mentioned that we must be able to identify a leap year from a common year, otherwise you would sometimes be a day out in reckoning. If the year divides by four and there is no remainder, then that is a Leap year, so go to the table for leap years. If there is a remainder, that is to say, if the remainder is greater than zero, then go to the table for Common years. Now you will have a better idea how the spreadsheet will calculate the number of days between one date and the next over the period of one year.
Believe it or not, you can insert the @LOOKUP command inside the @IF choosing function. In fact that is precisely what we are going to do to count the days. Study this expression. IF it is a leap year, then LOOKUP this table, else LOOKUP that table. Now in Spreadsheet language it is written like this:- @IF(F5<366,@LOOKUP(B5,B37...R37)+A5,@LOOKUP(B5,B40...R40)+A5
The formula looks complex but is not. It is simply clumsy, but it works and that's the main thing. Notice that I have used the "less than" mathematical sign "<" to determine the common year from the leap year. This formula fits in with little room to spare as you will see!
When one is creating a new spreadsheet and there are empty cells where figures are yet Not Available it pays to use the function @NA. If you do not, then formulae relating to this empty cell will go berserk. First of all though, let's provide for our headings to line up with the figures. Press Open-Apple V, L(abel), R(ight justify). All our headings will now line up on the right with the figures.
^The Tables^
Now to make sure our @LOOKUP formulae has something to look at, let's put in the lookup tables. Starting at cell block AH2, (30-odd cells to the right), put in MONTH, in AI2 put in JAN, in AJ2 put FEB. Instead of pressing Return for each entry, simply press Right Arrow. Your entry will be accepted and your cell cursor will be placed at the adjacent position for the next entry. Continue until all months are in place, ending with DEC in AT2.
Now leave a row blank and put in COMMON in AH4, 1 in AI4, 2 in AJ4, continue until 12 is in AT4. Now in AH5 put YEAR DAYS, in AI5 put 0, in AJ5 31, AK5 59, AL5 90, AM5 120, AN5 151, AO5 181, AP5 212, AQ5 243, AR5 273, AS5 304, AT5 334 . Now skip row 6. (This sounds like knitting.) In AH7 type LEAP, in AI7 1, AJ7 2, continue up to 12 which should end up in AT7. Now in AH8 type YEAR DAYS, in AI8 0, in AJ8 31, AK8 60, AL8 91, AM8 121, AN8 152, AO8 182, AP8 213, AQ8 244, AR8 274, AS8 305, AT8 335 . You have probably noticed the extra day count after February in the Leap year table.
^Protecting your entries^
Hopefully you have entered this table and corrected all errors. Now is the time to protect this block of data from being altered. Press OA-L, B, highlight the whole block, Return, P(rotection), N(othing). Now it will not be easy to
alter this table area. Do this with all of the formulae that you enter. This will ensure that your spreadsheet will remain reliable, unaltered perhaps by an overstrike cursor or wrong key press.
^Increasing Column Width^
It should not be necessary at this stage to change the width of the columns. The standard setting of 9 will do. When later you may have to increase the width of a column press OA-L, C, Return, C, OA-Arrow right to increase, Return.
^Typing in the Heading^
Now let's go to the heading. To try and make it clear in this article I have used a lower case alpha in front of each word block to mark the cell and a minus sign to indicate a blank cell. Do not type these symbols in or the commas either! We will work on one row at a time. Starting at cell block A1, B1, C1 etc. type in capitals each word followed by the Right Arrow:- aDAY bMONTH cYEAR dDOMESTIC eOFFPEAK fLEAP OR gDAY hBILLING iDOMESTIC jDOMESTIC kSEASON lDOMESTIC mBASE nDOMESTIC oDOMESTIC pDOMESTIC qOFFPEAK rOFFPEAK sSEASON tOFFPEAK uOFFPEAK vYEARLY wTOTAL xAVE/ANN yTOTAL zYEAR.
Now for row 2. a- b- c- dREADING eREADING fCOMMON gIN hDAYS iUNITS jUNITS k- lBASE mRATE nBASE oRATE pCHARGE qUNITS rUNITS s- tRATE uCHARGE vDAYS wUNITS xDAILY yFOR z-.
Row 3. a- b- c- d- e- fYEAR gYEAR h- iUSED jDAY k- lUNITS mPerUNIT nCHARGE oPerUNIT p- qUSED rPerDAY s- tPerUNIT u- vCHARGED wUSED xUNITS yYEAR z-.
Row 4. Starting with a quote symbol, fill A through Z with = signs.
^Entering the Formulae^
Before we do this, there are a couple of things we need to instruct the computer to do. One is to calculate in rows rather than columns. The other is not to calculate automatically every time you enter something, slowing down the whole job. Press OA-V, R(ecalculate), O(rder), R(ows). Press OA-V again, R, F(requency), M(anual.
If while entering a formula you get bipped, you have done something wrong, so double check your entry especially those brackets! Do not worry about the ERROR message in the cell. As long as we have those @NA's in place all is well.
Row 5. a@NA b@NA c@NA d@NA e@NA f@IF(C5/4>@INT(C5/4),365,366) g@IF(F5<366,@LOOKUP(B5,AI4..AT4)+A5,@LOOKUP(B5,AI7..AT7)+A5) h- i- j- k- l*** m*** n- o*** p- q- r- s- t*** u- v- w- x- y- z-.
Protect those formulae! Now for some copying. Place cursor on A5. Press OA-C, W, highlight 5 NA's, Return, D(own) A(rrow), .(point), DA, DA, DA, Return.
Place cursor on F5. OA-C, W, Return, DA, ., DA, DA, DA, Return, R, R, R, R.
Place cursor on G5. OA-C, W, Return, DA, ., DA, DA, DA, Return, R, R, N, N, R, N, N, R.
Note that when copying these formulae that this is the only place where you will answer NO CHANGE in the cell reference. Here it is referring to the tables we created. Since the table simply stays in the same place, the cell reference always remains the same. Do not worry about the ERROR prompts. As long as the formula remains unchanged in the lower left line on your screen when you have the cursor over the appropriate cell, all is still O.K. Be careful to get the formulae correct. The most common problem is getting the parentheses in their pairs. Look for pairs!
Place cursor on H6. Enter @IF(G6>G5,G6-G5,G6-G5+F5). Now copy. OA-C, W, Return, DA, ., DA, DA, Return, R, R, R, R, R, R, R.
Cursor on I6. @IF(D6<D5,0,D6-D5). Copy. OA-C, W, Return, DA, ., DA, DA, Return, R, R, R, R.
Cursor on J6. (I6/H6). Copy. OA-C, W, Return, DA, ., DA, DA, Return, R, R. Layout. OA-L, C, Return, V, F, 1 Return.
Place cursor on K6. Type in SUMMER. D(own) A(rrow). Type in AUTUMN, DA, WINTER, DA, SPRING.
At L6 type in 200. Repeat down to L9. This figure could be changed by SCC in the future so mark the top of this column with an asterisk if not already done.
At M6 enter 12.74 and repeat down to M9. Mark the top of this column also with asterisks.
At N6 type (L6*M6/100). Copy down to N9. All cell references are Relative. Layout for Dollars.
Cursor at O6. Enter 9.22 and copy down to O9 as before. This is the Domestic rate per unit. Asterisk markers above.
At P6 enter this:- -L6+I6*O6/100. Copy specifying Relative down to P9. Format for Dollars again. Watch alpha I and alpha O, They are not ones and zeros.
At Q6 enter @IF(E6<E5,0,E6-E5). Replicate down to Q9 and as before specify Relative.
At R6 Enter (Q6/H6). Copy down to R9. Layout for 1 decimal place.
Starting from S6 type SUMMER, AUTUMN, WINTER, SPRING down to S9.
At T6 enter the Offpeak Rate per unit 3.3. Copy down to T9. Place Asterisks above this one too. This Flag will make it easier to find when SCC increases its rates again.
At U6 type (Q6*T6/100). Copy down to U9 specifying R. Now format for Dollars.
At V9 enter @SUM(H6..H9). Did you get that in row 9?
At W6 enter (I6+Q6) and Copy down to W9
At X9, (W9/V9). Format for one decimal place.
At Y9, @SUM(N6..N9,P6..P9,U6..U9). Layout for Dollars. Also this column may have to be widened.
At Z9 type in (C9)
^Using Your New Creation^
Now you have your spreadsheet analyser for your quarterly electricity bill. All you have to do now is enter the figures in the first five columns.
Get out your first bill for 1990. On it you will see two reading dates. The first one is the Previous Reading date. Enter this date by numbers into the very first cells under the headings "day, month, year" right on top of the NA's to wipe them out.
Now look on your bill for the "Previous Reading" alongside Tariff 321. This is the Domestic Tariff. Type this into the Domestic Reading at cell D5. If you have Offpeak hot water this is the Previous Reading alongside Tariff 331, the Offpeak Tariff. Type this into the Offpeak Reading at cell E5. You will never have to enter a Previous Reading again on this spreadsheet.
You are now ready to enter the data that you should copy from each bill as you receive it. Enter the Present Reading date, the Domestic Tariff Reading and the Offpeak Tariff Reading.
That is all, just those five data from each bill. Now press OA-K to do the calculation. Now the full magic of the spreadsheet shows its power. All the analysis is presented to you in a matter of seconds!
From time to time you will notice a price change in the Cents Per kWh column. Enter these changes into the spreadsheet in their appropriate places. This is where the asterisk "flags" come in handy.
For each year you should copy the lower four rows further down on the spreadsheet for the next quarterly bills. After you do that, enter the @NA command into each of the first five columns, hitting OA-K to prepare for the next lot of entries.
Whenever you come across the @LOOKUP formula during replication, always remember to enter "No Change" when the references are to the Lookup Table. Everything else qualifies for the "Relative" instruction. It is a good idea to insert a blank row between the years for clarity. Do this only ^after^ replication.
^Watch For Anything Unusual^
As you study your spreadsheet, you will notice heavier demand for power in the winter months. Keep an eye on the Offpeak usage if you have it. Watch that this does not get too low in demand. If this happens, it is possible that your Offpeak switching is not operating properly. That means you will be paying the higher Domestic rate to heat your water.
Those of you who read this article outside New South Wales will have to change the values for your own local tariffs. This should not prove to be difficult, as most power reticulation bodies have a similar way of reckoning their charges.
Those who use gas could adapt the spreadsheet to function similarly. Unlike Telecom these organisations have meters
on your property which you can check at any time. This is a fair practice which avoids disputes. They also give you the last reading too, which means if there is a reading error, there will be no overlap charging as it will even out in the next bill.
^Printing it Out^
If you are fortunate enough to have Timeout Sidespread, this is an excellent program to use to print out this spreadsheet sideways on your fanfold paper. That way you can get an uninterrupted printout to study.
If not, then you will have to be content with printing out a few columns at a time and sticking them together later. Try to configure your printer to print out the most condensed characters it possibly can in order to keep your printout smaller and save those trees!
Permission is hereby granted for non-profit user groups to republish this content. PLEASE CREDIT THE AUTHOR AND THE SOURCE: Applecations, publication of the Apple Users' Group, Sydney, Australia